package com.qjcc.service.impl;

import com.alibaba.fastjson.JSONArray;
import com.qjcc.domain.AjaxJson;
import com.qjcc.service.QjztService;
import com.qjcc.util.OracleDataSourceFactory;
import com.qjcc.util.PinyinUtil;
import com.qjcc.util.QjztUtil;
import com.qjcc.util.SQLServerDataSourceFactory;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
@Transactional
public class QjztServiceImpl implements QjztService {

    @Autowired
    @Qualifier("primaryJdbcTemplate")
    protected JdbcTemplate primaryJdbc;

    @Autowired
    @Qualifier("secondaryJdbcTemplate")
    protected JdbcTemplate secondaryJdbc;

    private SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");

    /**
     * 首页信息化 客户数等数据（来自sqlserver）
     * @return
     */
    @Override
    public AjaxJson data() {
        AjaxJson j = new AjaxJson();
        Map<String, Object> m = new HashMap<>();

        SQLServerDataSourceFactory sdsf = SQLServerDataSourceFactory.getInstance();
        try {
            List<java.util.Map<String , Object>> list = sdsf.getTouyun();

//            String sql = "SELECT NY, MY_KEHU, MY_TOUYUN_ZONGSHU , MY_CHAOBIAO_SHU , MY_CHAOBIAO_CHENGGONG_SHU , SY_KEHU , SY_TOUYUN_ZONGSHU ," +
//                    "SY_CHAOBIAO_SHU , SY_CHAOBIAO_CHENGGONG_SHU , XXH_KEHU , XXH_YONGHU ,  XXH_GIS , XXH_GIS_CHANGDU ," +
//                    "XXH_GIS_SHEBEISHU , XXH_QIANGXIAN , XXH_QIANGXIAN_CISHU , XXH_SCADA , XXH_SCADA_XITONG , " +
//                    "XXH_SCADA_MENZHAN , XXH_SCADA_GUANWANG  XXH_SCADA_TIAOYA FROM test_data order by NY desc";
//            Query query = entityManager.createNativeQuery(sql);
//            query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
//            List<Map<String, Object>> list = query.getResultList();
            m.put("list", list);

            //取出第一条数据
            if (list.size() > 0) {
                Map<String, Object> data = list.get(0);
                m.put("data", data);
            }

            //循环list构建民用表的char
            List<String> categories = new ArrayList<>();
            for (int i = list.size() - 1; i >= 0; i--) {
                categories.add(list.get(i).get("NY") + "");
            }
            m.put("categories", categories);

            //投运量
            List<Double> tyl = new ArrayList<>();
            //抄表数
            List<Double> cbs = new ArrayList<>();
            //抄表成功数
            List<Double> cgs = new ArrayList<>();
            //抄表率
            List<Double> cbl = new ArrayList<>();

            for (int i = list.size() - 1; i >= 0; i--) {
                double tylCount = (Integer) list.get(i).get("MY_TOUYUN_ZONGSHU") / 10000;
                tyl.add(tylCount);

                double cbsCount = (Integer) list.get(i).get("MY_CHAOBIAO_SHU") / 10000;
                cbs.add(cbsCount);

                double cgsCount = (Integer) list.get(i).get("MY_CHAOBIAO_CHENGGONG_SHU") / 10000;
                cgs.add(cgsCount);

                double cblCount = QjztUtil.getPercent((Integer) list.get(i).get("MY_CHAOBIAO_CHENGGONG_SHU"), (Integer) list.get(i).get("MY_CHAOBIAO_SHU"));
                cbl.add(cblCount);
            }

            m.put("series1", tyl);
            m.put("series2", cbs);
            m.put("series3", cgs);
            m.put("series4", cbl);

            j.setAttributes(m);

        } catch (Exception e) {
            e.printStackTrace();
        }

        return j;
    }

    /**
     * 首页地图数据
     * @return
     */
    public AjaxJson mapData(){
        AjaxJson j = new AjaxJson();
        Date date = new Date();

        StringBuilder sql = new StringBuilder();
        sql.append("select '省份' as \"titleName\",  P.name as \"name\", NVL(Y.s_count, 0) as \"value\", NVL(X.o_count, 0) as \"o_count\", NVL(Y.s_count, 0) as \"s_count\" from ( ");
        sql.append(" select replace(replace(replace(replace(replace(replace(replace(t.TERRITORYNAME, '省', ''), '市', ''), '特别行政区', ''), '自治区', ''), '壮族', ''), '回族', ''), '维吾尔', '') as name from T_S_TERRITORY t where t.TERRITORYLEVEL = 2 ");
        sql.append(") P ");
        sql.append("left join ( ");
        sql.append(" select count(*) as o_count, replace(r.territoryname, '省', '') as o_province from cc_order c left join CC_AREA_SECTIONS s on c.AREA_SECTION_ID = s.id ");
        sql.append(" left join T_S_TERRITORY r on s.ADDRESS_SHENG = r.id ");
        sql.append(" group by r.territoryname ");
        sql.append(") X on P.name = X.o_province ");
        sql.append("left join ( ");
        sql.append(" select count(*) as s_count, s.province as s_province from CC_SIGN_STATISTICS s where to_char(s.STATISTICS_DATE, 'yyyy-mm-dd') = '" + sf.format(date) + "' group by s.province ");
        sql.append(") Y on P.name = Y.s_province");

        List<Map<String, Object>> result = secondaryJdbc.queryForList(sql.toString());
        for(Map<String , Object> obj: result){
            String drilldown = PinyinUtil.getPingYin(obj.get("name").toString());
            obj.put("drilldown", drilldown);
        }

        j.setObj(result);
        return j;
    }

    /**
     * 省地图数据
     * @param provinceName
     * @return
     */
    public AjaxJson mapDataDrilldown(String provinceName){
        AjaxJson j = new AjaxJson();
        Date date = new Date();

        StringBuilder sql = new StringBuilder();
        sql.append("select '地市' as \"titleName\", P1.\"name\" as \"name1\", P2.\"name\" as \"name2\", 0 as \"value\", NVL(P2.o_count, 0) as \"o_count\", NVL(P1.s_count, 0) as \"s_count\" from ( ");
        sql.append("    select s.CITY as \"name\", count(*) as s_count from CC_SIGN_STATISTICS s where to_char(s.STATISTICS_DATE, 'yyyy-mm-dd') = '" + sf.format(date) + "' and s.PROVINCE = '四川' group by s.CITY ");
        sql.append(") P1 ");
        sql.append("full join ( ");
        sql.append("    select count(*) o_count, replace(rc.territoryname, '市', '') as \"name\" from cc_order c left join CC_AREA_SECTIONS s on c.AREA_SECTION_ID = s.id ");
        sql.append("    left join T_S_TERRITORY rc on s.ADDRESS_SHI = rc.id ");
        sql.append("    left join T_S_TERRITORY rs on s.ADDRESS_SHENG = rs.id  where c.status = '1' and rs.territoryname = '四川省' group by rc.territoryname ");
        sql.append(") P2 on P1.\"name\" =  P2.\"name\"");

        List<Map<String, Object>> result = secondaryJdbc.queryForList(sql.toString());
        j.setObj(result);
        return j;
    }


}
